package com.fdms.aurora;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Properties;

import org.apache.commons.lang.math.NumberUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.fdms.connectivity.ConnectionHandler;

public class FirstCallInformation {
	private String templateName;
	private Connection cn = null;
	private PreparedStatement st = null;
	private Statement stm=null;
	private ResultSet rs=null;
	private Properties properties = new Properties();
	private HashMap<String,String> chapelLocations =null;
	private HashMap<String, String> directors =null;
	private String chapelId;
	private String arrangerId;
	private int chapelIndex;
	private int arrangerIndex;
	
	public FirstCallInformation(){
		try {
			properties.load(new FileInputStream(new File("validationFirstCallInfo.properties")));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	public void startProcessRead(String templateName){
		// get excel file
		this.templateName= templateName;
		
		int startRow =0;
		int index =0;
		int totalCaseAdded=0;
		int intValue=0;
		Utility.initRejectXLS();
		getChapelLocations();
		getDirctors();
		try{
			cn = ConnectionHandler.getConnection();
			StringBuffer query = new StringBuffer("INSERT INTO vitalstats(");
			StringBuffer queryValues = new StringBuffer();
			StringBuffer finalQuery = new StringBuffer();
			String fieldValue="";

			InputStream myxls = new FileInputStream(templateName);
			XSSFWorkbook wb = new XSSFWorkbook(myxls);

			XSSFSheet sheet = wb.getSheet("FirstCallInformation");       // first sheet
			XSSFRow row     = sheet.getRow(1);        // column/field row
		        
			//Field row
			HashMap<Integer,String> validation= new HashMap<Integer,String>();
			// read column names
			for( index = 0; index <  63; index++) {
				fieldValue=row.getCell(index).getStringCellValue();
		
				if(properties.containsKey(fieldValue)){
					validation.put(index, fieldValue);
					//System.out.println("added validation "+ index);
				}
				
				if(index < 62){
					query.append( fieldValue + ",");
				}else{
					query.append(fieldValue+ ",arrangerid,chapelnumber) VALUES (");
				}
			}
			 /* it = validation.entrySet().iterator();
			  while (it.hasNext()) {
			        Map.Entry pairs = (Map.Entry)it.next();
			        System.out.println(pairs.getKey() + " = " + pairs.getValue());
			        //it.remove(); // avoids a ConcurrentModificationException
			    }*/
			//System.out.println("validation size :: "+ validation.size() );

			// read data from excel rows
			XSSFRow data=null;
			System.out.println("total no of rows :: "+ sheet.getLastRowNum() );
			
			ArrayList<Integer> validRows= checkVailidRows(sheet,validation);
			for( startRow= 0;startRow<validRows.size();startRow++ ){
				try {
					data=sheet.getRow(validRows.get(startRow).intValue());
					queryValues= new StringBuffer();
					XSSFCell cell = null;
					System.out.println("last cell num :: "+ data.getLastCellNum());
						for(index = 0; index < 63; index++) {
							 cell = data.getCell(index);
							    if(cell.getCellType()==5) //Error value
							    	queryValues.append("'',");
							    else if(cell.getCellType()==3) //null value
							    	queryValues.append("'',");
							    else if (cell.getCellType()==0){ // Numeric value
							    	intValue=(int)cell.getNumericCellValue() ;
							    	queryValues.append(intValue + ",");
							    }else if (cell.getCellType()==1) { // String value
							    	if(cell.getStringCellValue().equals("(NULL)")){
							    		queryValues.append("'',");
							    	}else{
							    		queryValues.append( "'"+cell.getStringCellValue() + "',");
							    	}
							    }else if (cell.getCellType()==4) // boolean value
							    	queryValues.append( cell.getBooleanCellValue() + ",");
						}
						cell = data.getCell(chapelIndex);
						setChapelId(chapelLocations.get(cell.getStringCellValue()));
						cell = data.getCell(arrangerIndex);
						setArrangerId(directors.get(cell.getStringCellValue()));
						
						
				}catch(Exception ex){
					System.out.println("Error for reading excel row " + startRow + " and column "+ index );
					System.out.println("Error :: "+ ex.getMessage());
					break;
				}
				finalQuery.append(query);
				finalQuery.append(queryValues);
				finalQuery.append(getArrangerId()+","+getChapelId()+")");
				//System.out.println(" Query :: "+ finalQuery);
				// selected row will be inserted into database
				int vitalsMasterKey = 0;
				try{
			        //st = cn.createStatement();
					st = cn.prepareStatement(finalQuery.toString(), Statement.RETURN_GENERATED_KEYS);
			        int affectedRow = st.executeUpdate();
			        
			        if(affectedRow == 0) {
			        	System.out.println("Insertion failed.");
			        } else {
			        	totalCaseAdded++;
			        	ResultSet generatedKeys = st.getGeneratedKeys();
			            if (generatedKeys.next()) {
			            	vitalsMasterKey = generatedKeys.getInt(1);
			            	System.out.println("New VitalsMasterKey => " + vitalsMasterKey);
			            }
			            
			            if(vitalsMasterKey != 0) {
			            	String personQuery = "INSERT INTO person(VitalsMasterKey, PersonTypeId, ExecutorFirstName, ExecutorLastName, ExecutorStreet, ExecutorStreet2, ExecutorStreet3, ExecutorCity, ExecutorState, ExecutorZip, ExecutorPhone, ExecutorRelation, ExecutorSame, ExecutorEmail, ExecutorCellPhone) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			            	st = cn.prepareStatement(personQuery);
			            	st.setInt(1, vitalsMasterKey);
			            	st.setInt(2, 1);
			            	st.setString(3, data.getCell(63).getStringCellValue());
			            	st.setString(4, data.getCell(64).getStringCellValue());
			            	st.setString(5, data.getCell(65).getStringCellValue());
			            	st.setString(6, data.getCell(66).getStringCellValue());
			            	st.setString(7, data.getCell(67).getStringCellValue());
			            	st.setString(8, data.getCell(68).getStringCellValue());
			            	st.setString(9, data.getCell(69).getStringCellValue());
			            	data.getCell(70).setCellType(Cell.CELL_TYPE_STRING);
			            	st.setString(10, data.getCell(70).getStringCellValue());
			            	st.setString(11, data.getCell(71).getStringCellValue());
			            	st.setString(12, data.getCell(72).getStringCellValue());
			            	st.setString(13, "N");
			            	st.setString(14, data.getCell(73).getStringCellValue());
			            	st.setString(15, data.getCell(74).getStringCellValue());
			            	st.execute();
			            }
			          //financial table
			           if(vitalsMasterKey != 0) {
			            	String financialQuery = "INSERT INTO financialdata(VitalsMasterKey,SaleType,CalcFinanceChrgYN,FinanceChargeRate,SaleDate,DueDate,DateInvoiceSent,AmountFinanced,InterestTotal,Term,MonthlyPaymentAmt,LastFinancedPmt,FirstPaymentDueDate,TotalPaidToDate,FirstFinChargCalc,FinChargeSchedule,FinChargeLastCalc,EagleApplNo,EagleAuthCode,LastPaymentRespons,LastPaymentMemo,LastPaymentAmount,LastPaymentDate,SentToAccounting,TotalCharges,LastFinanceCharge,LastReceiptNo,FileVersion,MiscNotes,PriceListTable,PackageTable,FamilyUsedUsBefore,FnrlHomePrevUsed,AdvSource,DateModified,TimeModified,SalesDescCDID,FinChargePreviousCalc,PreviousFinanceCharge) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			            	st = cn.prepareStatement(financialQuery);
			            	st.setInt(1, vitalsMasterKey);
			            	st.setString(2, data.getCell(75).getStringCellValue());
			            	st.setInt(3, (int)data.getCell(76).getNumericCellValue());
			            	st.setInt(4, (int)data.getCell(77).getNumericCellValue());
			            	st.setString(5, data.getCell(78).getStringCellValue());
			            	st.setString(6, data.getCell(79).getStringCellValue());
			            	st.setString(7, data.getCell(80).getStringCellValue());
			            	st.setInt(8, NumberUtils.toInt(Double.toString(data.getCell(81).getNumericCellValue()) ,0));
			            	st.setInt(9, NumberUtils.toInt(Double.toString(data.getCell(82).getNumericCellValue()),0));
			            	st.setInt(10, NumberUtils.toInt(Double.toString(data.getCell(83).getNumericCellValue()),0));
			            	st.setInt(11, NumberUtils.toInt(Double.toString(data.getCell(84).getNumericCellValue()),0));
			            	st.setInt(12, NumberUtils.toInt(Double.toString(data.getCell(85).getNumericCellValue()),0));
			            	st.setString(13, data.getCell(86).getStringCellValue());
			            	st.setInt(14, NumberUtils.toInt(Double.toString(data.getCell(87).getNumericCellValue()),0));
			            	st.setString(15, data.getCell(88).getStringCellValue());
			            	st.setString(16, data.getCell(89).getStringCellValue());
			            	st.setString(17, data.getCell(90).getStringCellValue());
			            	st.setString(18, data.getCell(91).getStringCellValue());
			            	st.setString(19, data.getCell(92).getStringCellValue());
			            	st.setString(20, data.getCell(93).getStringCellValue());
			            	st.setString(21, data.getCell(94).getStringCellValue());
			            	st.setInt(22, NumberUtils.toInt(Double.toString(data.getCell(95).getNumericCellValue()),0));
			            	st.setString(23, data.getCell(96).getStringCellValue());
			            	st.setInt(24, NumberUtils.toInt(Double.toString(data.getCell(97).getNumericCellValue()),0));
			            	st.setInt(25, NumberUtils.toInt(Double.toString(data.getCell(98).getNumericCellValue()),0));
			            	st.setInt(26, NumberUtils.toInt(Double.toString(data.getCell(99).getNumericCellValue()),0));
			            	st.setInt(27, NumberUtils.toInt(Double.toString(data.getCell(100).getNumericCellValue()),0));
			            	st.setInt(28, NumberUtils.toInt(Double.toString(data.getCell(101).getNumericCellValue()),0));
			            	st.setString(29, data.getCell(102).getStringCellValue());
			            	st.setString(30, data.getCell(103).getStringCellValue());
			            	st.setString(31, data.getCell(104).getStringCellValue());
			            	st.setString(32, data.getCell(105).getStringCellValue());
			            	st.setString(33, data.getCell(106).getStringCellValue());
			            	st.setString(34, data.getCell(107).getStringCellValue());
			            	st.setString(35, data.getCell(108).getStringCellValue());
			            	st.setString(36, data.getCell(109).getStringCellValue());
			            	st.setInt(37, NumberUtils.toInt(Double.toString(data.getCell(110).getNumericCellValue()),0));
			            	st.setInt(38, NumberUtils.toInt(Double.toString(data.getCell(111).getNumericCellValue()),0));
			            	st.setInt(39, NumberUtils.toInt(Double.toString(data.getCell(112).getNumericCellValue()),0));
			            	st.execute();
			            }
			        }
				}catch(Exception ex){
					ex.printStackTrace();
					System.out.println("Error inserting record " + startRow + " and column "+ index );
					System.out.println("Error :: "+ ex.getMessage());
				}
				finalQuery = new StringBuffer();
			}
			
			System.out.println("Total Cases added in DB are :: "+ totalCaseAdded);
		}catch(Exception ex){
			System.out.println("Error in row " + startRow + " and column "+ index );
			System.out.println("Error :: "+ ex.getMessage());
		}
		// rejected row will be written in reject excel file
		Utility.createRejectXls();		
	}
	
	public HashMap<String,String> getChapelLocations() {
		chapelLocations = new HashMap<String, String>();
		chapelLocations.put("Westside Funeral Home","5");
		chapelLocations.put("Eastside Funeral Home","4");
		return chapelLocations;
	}

	public HashMap<String,String> getDirctors() {
		directors = new HashMap<String, String>();
		directors.put("Brandei F. Bass","3");
		directors.put("Fred Murray","2");
		return directors;
	}
	
	public ArrayList<Integer> checkVailidRows(XSSFSheet sheet,HashMap<Integer,String> validation){ 
		ArrayList<Integer> validRows= new ArrayList<Integer>();
		XSSFRow data=null;
			for( int startRow= 2;startRow<=sheet.getLastRowNum();startRow++ ){
				try {
					data=sheet.getRow(startRow);
					boolean validRowData = true;
					for (Integer key : validation.keySet()) {
					   int colIndex=key.intValue();
					   //System.out.println("colINdex :: " + colIndex);
					   XSSFCell cell = data.getCell(colIndex);
						// validate data of excel row
					  // System.out.println("get Validate on : "+ validation.get(key));
					   	if(performValidation(validation.get(key), data,cell)==true){
					   		validRowData=true;
					   	}else{
					   		validRowData=false;
					   		System.out.println("Validation fail on row :: "+ data.getRowNum());
					   		break;
					   	}
					}
					if(validRowData){
						//System.out.println("Valid rows no is :: "+ startRow);
						validRows.add(Integer.valueOf(startRow));
					}
				}catch(Exception ex){
					ex.printStackTrace();
				}
			}
		 return validRows;
	 }

	public boolean performValidation(String validationName,XSSFRow row,XSSFCell cell){
		//System.out.println("Validation perform Name :: "+validationName);
		if(validationName.equalsIgnoreCase("ChapelLocation")){
			if(cell==null || cell.getStringCellValue().equals("(NULL)")||cell.getStringCellValue().trim().length()<=0){
				Utility.addErrorRow(row);
				Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
				Utility.rowCount++;
			}else if(!chapelLocations.containsKey(cell.getStringCellValue())){
				Utility.addErrorRow(row);
				Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
				Utility.rowCount++;
			}else{
				//setChapelId(chapelLocations.get(cell.getStringCellValue()));
				chapelIndex= cell.getColumnIndex();
				return true;
			}
		}else if(validationName.equalsIgnoreCase("DirectorName")){
			if(cell==null || cell.getStringCellValue().equals("(NULL)")||cell.getStringCellValue().trim().length()<=0){
				Utility.addErrorRow(row);
				Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
				Utility.rowCount++;
			}else if(!directors.containsKey(cell.getStringCellValue())){
				Utility.addErrorRow(row);
				Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
				Utility.rowCount++;
			}else{
				//setArrangerId(directors.get(cell.getStringCellValue()));
				arrangerIndex=cell.getColumnIndex();
				return true;
			}
		}else if(validationName.equalsIgnoreCase("DeceasedFirstName")){
			//System.out.println("DeceasedFirstName :: "+cell.getStringCellValue());
			if(cell==null || cell.getStringCellValue().equals("(NULL)")||cell.getStringCellValue().trim().length()<=0){
				Utility.addErrorRow(row);
				Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
				Utility.rowCount++;
			}else{
				return true;
			}
		}else if(validationName.equalsIgnoreCase("DeceasedLastName")){
			if(cell==null || cell.getStringCellValue().equals("(NULL)")||cell.getStringCellValue().trim().length()<=0){
				Utility.addErrorRow(row);
				Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
				Utility.rowCount++;
			}else{
				return true;
			}
		}else if(validationName.equalsIgnoreCase("DeceasedFullName")){
			if(cell==null || cell.getStringCellValue().equals("(NULL)")||cell.getStringCellValue().trim().length()<=0){
				Utility.addErrorRow(row);
				Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
				Utility.rowCount++;
			}else{
				return true;
			}
		}else if(validationName.equalsIgnoreCase("FacilityName")){
			if(cell==null || cell.getStringCellValue().equals("(NULL)")||cell.getStringCellValue().trim().length()<=0){
				Utility.addErrorRow(row);
				Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
				Utility.rowCount++;
			}else{
				return true;
			}
		}else if(validationName.equalsIgnoreCase("AgeYears")){
			if(cell==null){
				Utility.addErrorRow(row);
				Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
				Utility.rowCount++;
			}else{
				try{
					cell.getNumericCellValue();
					return true;
				}catch(Exception ex){
					Utility.addErrorRow(row);
					Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
					Utility.rowCount++;
					return false;
				}
				
			}
		}else if(validationName.equalsIgnoreCase("PreneedArrngDate")){
			if(cell==null || cell.getStringCellValue().trim().length()<=0){
				Utility.addErrorRow(row);
				Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
				Utility.rowCount++;
			}else if(cell.getStringCellValue().equals("(NULL)")) {
				return true;
			}else
				try{
					Utility.convertToDateMMDDYYYY(cell.getStringCellValue());
					return true;
				}catch(Exception ex){
					//ex.printStackTrace();
					Utility.addErrorRow(row);
					Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
					Utility.rowCount++;
					return false;
				}
		}else if(validationName.equalsIgnoreCase("DateOfBirth")){
			if(cell==null || cell.getStringCellValue().trim().length()<=0){
				Utility.addErrorRow(row);
				Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
				Utility.rowCount++;
			}else if(cell.getStringCellValue().equals("(NULL)")) {
				return true;
			}else
				try{
					Utility.convertToDateMMDDYYYY(cell.getStringCellValue());
					return true;
				}catch(Exception ex){
					Utility.addErrorRow(row);
					Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
					Utility.rowCount++;
					return false;
				}
		}else if(validationName.equalsIgnoreCase("DateOfDeath")){
			if(cell==null || cell.getStringCellValue().trim().length()<=0){
				Utility.addErrorRow(row);
				Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
				Utility.rowCount++;
			}else if(cell.getStringCellValue().equals("(NULL)")) {
				return true;
			}else
				try{
					Utility.convertToDateMMDDYYYY(cell.getStringCellValue());
					return true;
				}catch(Exception ex){
					Utility.addErrorRow(row);
					Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
					Utility.rowCount++;
					return false;
				}
		}else if(validationName.equalsIgnoreCase("BurialDate")){
			if(cell==null || cell.getStringCellValue().trim().length()<=0){
				Utility.addErrorRow(row);
				Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
				Utility.rowCount++;
			}else if(cell.getStringCellValue().equals("(NULL)")) {
				return true;
			}else
				try{
					Utility.convertToDateMMDDYYYY(cell.getStringCellValue());
					return true;
				}catch(Exception ex){
					Utility.addErrorRow(row);
					Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
					Utility.rowCount++;
					return false;
				}
		}else if(validationName.equalsIgnoreCase("DispositionDate")){
			if(cell==null || cell.getStringCellValue().trim().length()<=0){
				Utility.addErrorRow(row);
				Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
				Utility.rowCount++;
			}else if(cell.getStringCellValue().equals("(NULL)")) {
				return true;
			}else
				try{
					Utility.convertToDateMMDDYYYY(cell.getStringCellValue());
					return true;
				}catch(Exception ex){
					Utility.addErrorRow(row);
					Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
					Utility.rowCount++;
					return false;
				}
		}else if(validationName.equalsIgnoreCase("ContractCode")){
			if(cell==null || cell.getNumericCellValue()<=0){
				Utility.addErrorRow(row);
				Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
				Utility.rowCount++;
			}else if(cell.getNumericCellValue()>0) {
				try{
					int intValue=(int)cell.getNumericCellValue() ;
			    	stm= cn.createStatement();
			    	rs= stm.executeQuery("SELECT COUNT(*) FROM vitalstats WHERE ContractCode = '"+ intValue +"';");
			    	rs.next();
			    	if(rs.getInt(1)>0){
			    		Utility.addErrorRow(row);
						Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
						Utility.rowCount++;
						return false;
			    	}else{
			    		return true;
			    	}
				}catch(Exception ex){
					Utility.addErrorRow(row);
					Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
					Utility.rowCount++;
					return false;
				}
			}
		}else if(validationName.equalsIgnoreCase("CaseCode")){
			if(cell==null || cell.getNumericCellValue()<=0){
				Utility.addErrorRow(row);
				Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
				Utility.rowCount++;
			}else if(cell.getNumericCellValue()>0) {
				try{
					int intValue=(int)cell.getNumericCellValue() ;
			    	stm= cn.createStatement();
			    	rs= stm.executeQuery("SELECT COUNT(*) FROM vitalstats WHERE ContractCode = '"+ intValue +"';");
			    	rs.next();
			    	if(rs.getInt(1)>0){
			    		Utility.addErrorRow(row);
						Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
						Utility.rowCount++;
						return false;
			    	}else{
			    		return true;
			    	}
				}catch(Exception ex){
					Utility.addErrorRow(row);
					Utility.addErrorDescriptionRow(row.getRowNum(),validationName, properties.getProperty(validationName));
					Utility.rowCount++;
					return false;
				}
			}
		}
		return false;
	}
	public String getChapelId() {
		return chapelId;
	}
	public void setChapelId(String chapelId) {
		this.chapelId = chapelId;
	}
	public String getArrangerId() {
		return arrangerId;
	}
	public void setArrangerId(String arrangerId) {
		this.arrangerId = arrangerId;
	}
}
